package softarch.portal.db.flatfile;

import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Vector;

import org.relique.jdbc.csv.CsvDriver;

import softarch.portal.data.Article;
import softarch.portal.data.Book;
import softarch.portal.data.CheapSubscription;
import softarch.portal.data.Conference;
import softarch.portal.data.ExpensiveSubscription;
import softarch.portal.data.ExpertAdministrator;
import softarch.portal.data.ExternalAdministrator;
import softarch.portal.data.FreeSubscription;
import softarch.portal.data.InterestingWebsite;
import softarch.portal.data.Operator;
import softarch.portal.data.RawData;
import softarch.portal.data.RegularAdministrator;
import softarch.portal.data.RegularData;
import softarch.portal.data.Report;
import softarch.portal.data.SoftwareRepository;
import softarch.portal.data.UserProfile;
import softarch.portal.db.ScriptRunner;
import softarch.portal.db.DatabaseException;

public class CSVDatabase {
	String dbUrl;
	
	public CSVDatabase(String dbUser, String password, String dbUrl, String dbInitFile) {
		Class.forName("org.relique.jdbc.csv.CsvDriver");
		this.dbUrl = dbUrl;
		Connection c = DriverManager.getConnection("jdbc:relique:csv:" + dbUrl);
		ScriptRunner runner = new ScriptRunner(c, True, True);
		runner.runScript(new BufferedReader(new FileReader(dbInitFile)));
	}
	
	public void insertUserProfile(UserProfile profile) {
		executeResultlessSQL(profile.asSql());
	}

	public void updateUserProfile(UserProfile profile) {
		executeResultlessSQL(profile.asSqlUpdate());
	}

	public UserProfile findUser(String username) throws DatabaseException {
		try {
			ResultSet resultSet;
			
			resultSet = executeSQLWithResult(
					"SELECT * FROM FreeSubscription WHERE " +
					"Username = \'" + username + "\';"
					);
			if (resultSet.first())
				return new FreeSubscription(resultSet);
			//
			resultSet = executeSQLWithResult(
					"SELECT * FROM CheapSubscription WHERE " +
							"Username = \'" + username + "\';"
					);
			if (resultSet.first()) {
				return new CheapSubscription(resultSet);
			}
			//
			resultSet = executeSQLWithResult(
					"SELECT * FROM ExpensiveSubscription WHERE " +
					"Username = \'" + username + "\';"
				);
			if (resultSet.first()) {
				return new ExpensiveSubscription(resultSet);
			}
			//
			resultSet = executeSQLWithResult(
					"SELECT * FROM Operator WHERE " +
					"Username = \'" + username + "\';"
					);
			if (resultSet.first())
				return new Operator(resultSet);
			//
			resultSet = executeSQLWithResult(
					"SELECT * FROM ExternalAdministrator WHERE " +
					"Username = \'" + username + "\';"
				);
			if (resultSet.first())
				return new ExternalAdministrator(resultSet);
			//
			resultSet = executeSQLWithResult(
					"SELECT * FROM RegularAdministrator WHERE " +
					"Username = \'" + username + "\';"
					);
			if (resultSet.first())
				return new RegularAdministrator(resultSet);
			//
			resultSet = executeSQLWithResult(
					"SELECT * FROM ExpertAdministrator WHERE " +
					"Username = \'" + username + "\';"
					);
			if (resultSet.first())
				return new ExpertAdministrator(resultSet);
			
			throw new DatabaseException("Invalid username.");
			
		} catch (Exception e) {
			throw new DatabaseException("CSV exception: " + e.getMessage());
		}
	}

	public boolean userExists(String username) throws DatabaseException {
		try {
			ResultSet resultSet;
			
			resultSet = executeSQLWithResult(
					"SELECT * FROM FreeSubscription WHERE " +
					"Username = \'" + username + "\';");
				if (resultSet.first())
					return true;

				resultSet = executeSQLWithResult(
					"SELECT * FROM CheapSubscription WHERE " +
					"Username = \'" + username + "\';");
				if (resultSet.first())
					return true;

				resultSet = executeSQLWithResult(
					"SELECT * FROM ExpensiveSubscription WHERE " +
					"Username = \'" + username + "\';");
				if (resultSet.first())
					return true;

				resultSet = executeSQLWithResult(
					"SELECT * FROM Operator WHERE " +
					"Username = \'" + username + "\';");
				if (resultSet.first())
					return true;

				resultSet = executeSQLWithResult(
					"SELECT * FROM ExternalAdministrator WHERE " +
					"Username = \'" + username + "\';");
				if (resultSet.first())
					return true;

				resultSet = executeSQLWithResult(
					"SELECT * FROM RegularAdministrator WHERE " +
					"Username = \'" + username + "\';");
				if (resultSet.first())
					return true;

				resultSet = executeSQLWithResult(
					"SELECT * FROM ExpertAdministrator WHERE " +
					"Username = \'" + username + "\';");
				if (resultSet.first())
					return true;

				return false;
		} catch (Exception e) {
			throw new DatabaseException(
					"CSV Exception: " + e.getMessage());
		}
	}

	public List findRecords(String informationType, String queryString) throws DatabaseException {
		queryString = "%" + queryString + "%";
		
		try {
			List result = new Vector();
			ResultSet resultSet;
			
			switch (informationType.charAt(0)) {
			case 'B':
				resultSet = executeSQLWithResult("SELECT * FROM Book WHERE Author LIKE \'" + queryString + "\' OR Publisher LIKE \'" + queryString + "\' OR Review LIKE \'" + queryString + "\' OR Summary LIKE \'" + queryString + "\' OR Title LIKE \'" + queryString + "\' ORDER BY DateAdded DESC;");
				while (resultSet.next())
					result.add(new Book(resultSet));
				return result;
			case 'A':
				resultSet = executeSQLWithResult("SELECT * FROM Article WHERE MATCH (Author, Review, Summary, Title) AGAINST (\'" + queryString + "\' IN BOOLEAN MODE) ORDER BY DateAdded DESC;");
				while (resultSet.next())
					result.add(new Article(resultSet));
				return result;
			case 'R':
				resultSet = executeSQLWithResult("SELECT * FROM Report WHERE MATCH (Author, Review, Summary, Title) AGAINST (\'" + queryString + "\' IN BOOLEAN MODE) ORDER BY DateAdded DESC;");
				while (resultSet.next())
					result.add(new Report(resultSet));
				return result;
			case 'C':
				resultSet = executeSQLWithResult("SELECT * FROM Conference WHERE MATCH (Location, Name, Review, Summary) AGAINST (\'" + queryString + "\' IN BOOLEAN MODE) ORDER BY DateAdded DESC;");
				while (resultSet.next())
					result.add(new Conference(resultSet));
				return result;
			case 'S':
				resultSet = executeSQLWithResult("SELECT * FROM SoftwareRepository WHERE MATCH (Author, Name) AGAINST (\'" + queryString + "\' IN BOOLEAN MODE) ORDER BY DateAdded DESC;");
				while (resultSet.next())
					result.add(new SoftwareRepository(resultSet));
				return result;
			case 'I':
				resultSet = executeSQLWithResult("SELECT * FROM InterestingWebsite WHERE MATCH (Author, Review, Summary, Title) AGAINST (\'" + queryString + "\' IN BOOLEAN MODE) ORDER BY DateAdded DESC;");
				while (resultSet.next())
					result.add(new InterestingWebsite(resultSet));
				return result;
			default:
				throw new DatabaseException(
					"Invalid information type!");
		}
			 
		} catch (Exception e) {
			throw new DatabaseException(
					"CSV Exception: " + e.getMessage());
		}
	}

	public List findRecordsFrom(String informationType, Date date) throws DatabaseException {
		try {
			List result = new Vector();

			ResultSet resultSet;

			DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			
			String query =	"SELECT * FROM " + informationType +
					" WHERE DateAdded >= \'" +
					df.format(date) + "\' ORDER BY " +
					"DateAdded DESC;";

			switch (informationType.charAt(0)) {
				case 'B':
					resultSet = executeSQLWithResult(query);
					while (resultSet.next())
						result.add(new Book(resultSet));
					return result;
				case 'A':
					resultSet = executeSQLWithResult(query);
					while (resultSet.next())
						result.add(new Article(resultSet));
					return result;
				case 'R':
					resultSet = executeSQLWithResult(query);
					while (resultSet.next())
						result.add(new Report(resultSet));
					return result;
				case 'C':
					resultSet = executeSQLWithResult(query);
					while (resultSet.next())
						result.add(new Conference(resultSet));
					return result;
				case 'S':
					resultSet = executeSQLWithResult(query);
					while (resultSet.next())
						result.add(new SoftwareRepository(resultSet));
					return result;
				case 'I':
					resultSet = executeSQLWithResult(query);
					while (resultSet.next())
						result.add(new InterestingWebsite(resultSet));
					return result;
				default:
					throw new DatabaseException(
						"Invalid information type!");
			}
		}

		catch (Exception e) {
			throw new DatabaseException(
				"CSV Exception: " + e.getMessage());
		}
	}

	public void addRegularData(RegularData rd) {
		executeResultlessSQL(rd.asSql());
	}

	public int getNumberOfRegularRecords(String informationType) throws DatabaseException {
		try {
			ResultSet resultSet = executeSQLWithResult(
				"SELECT COUNT(*) \"Count\" FROM " +
				informationType + ";");
			resultSet.first();
			return resultSet.getInt("Count");
		}

		catch (Exception e) {
			throw new DatabaseException(
				"CSV Exception: " + e.getMessage());
		}
	}

	public List getRawData() throws DatabaseException {
		try {
			List result = new Vector();
			
			ResultSet resultSet
				= executeSQLWithResult("SELECT * FROM Raw WHERE Structured <> 1;");
			while (resultSet.next()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				result.add(rawData);
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawBook;");
			while (resultSet.next()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				result.add(rawData.setStructure(new Book(resultSet)));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawArticle;");
			while (resultSet.next()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				result.add(rawData.setStructure(
							new Article(resultSet)));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawReport;");
			while (resultSet.next()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				result.add(rawData.setStructure(
							new Report(resultSet)));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawConference;");
			while (resultSet.next()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				result.add(rawData.setStructure(
							new Conference(resultSet)));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawSoftwareRepository;");
			while (resultSet.next()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				result.add(
					rawData.setStructure(
						new SoftwareRepository(resultSet)));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawInterestingWebsite;");
			while (resultSet.next()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				result.add(
					rawData.setStructure(
						new InterestingWebsite(resultSet)));
			}
			
			return result;
		}

		catch (Exception e) {
			throw new DatabaseException(
				"CSV Exception: " + e.getMessage());
		}
	}

	private int getNewId()
			throws DatabaseException {

			try {

				ResultSet resultSet = executeSQLWithResult("SELECT MAX(ID) AS ID FROM Raw;");

				if (resultSet.first())
					return resultSet.getInt("ID") + 1;
				else
					throw new DatabaseException("The raw database could not find a new id!");
			}

			catch (Exception e) {
				throw new DatabaseException(
					"CSV Exception: " + e.getMessage());
			}
		}

	
	public RawData getRawData(int id) throws DatabaseException {
		try {

			ResultSet resultSet = executeSQLWithResult("SELECT * FROM Raw WHERE Structured <> 1 AND ID = " + id + ";");
			if (resultSet.first()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				return rawData;
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawBook WHERE Raw.ID = " + id + ";");
			if (resultSet.first()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				return rawData.setStructure(new Book(resultSet));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawArticle WHERE Raw.ID = " + id + ";");
			if (resultSet.first()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				return rawData.setStructure(new Article(resultSet));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawReport WHERE Raw.ID = " + id + ";");
			if (resultSet.first()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				return rawData.setStructure(new Report(resultSet));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawConference WHERE Raw.ID = " + id + ";");
			if (resultSet.first()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				return rawData.setStructure(new Conference(resultSet));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawSoftwareRepository WHERE Raw.ID = " + id + ";");
			if (resultSet.first()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				return rawData.setStructure(new SoftwareRepository(resultSet));
			}

			resultSet = executeSQLWithResult("SELECT * FROM Raw NATURAL JOIN RawInterestingWebsite WHERE Raw.ID = " + id + ";");
			if (resultSet.first()) {
				RawData rawData
					= new RawData(	resultSet.getInt("ID"),
							resultSet.getString("Source"));
				return rawData.setStructure(new InterestingWebsite(resultSet));
			}

			throw new DatabaseException("The raw data object with ID " + id + " was not found in the database.");
		}

		catch (Exception e) {
			throw new DatabaseException(
				"CSV Exception: " + e.getMessage());
		}
	}

	public void addRawData(RegularData rd) throws DatabaseException {
		int id = getNewId();
		RawData rawData = new RawData(id, rd);
		for (Object o : rawData.asSql()) {
			executeResultlessSQL((String) o);
		}
	}

	public void deleteRawData(RawData rd) {
		int id = rd.getId();
		executeResultlessSQL("DELETE FROM Raw WHERE ID = " + id + ";");
		executeResultlessSQL("DELETE FROM RawBook WHERE ID = " + id + ";");
		executeResultlessSQL("DELETE FROM RawArticle WHERE ID = " + id + ";");
		executeResultlessSQL("DELETE FROM RawReport WHERE ID = " + id + ";");
		executeResultlessSQL("DELETE FROM RawConference WHERE ID = " + id + ";");
		executeResultlessSQL("DELETE FROM RawSoftwareRepository WHERE ID = " + id + ";");
		executeResultlessSQL("DELETE FROM RawInterestingWebsite WHERE ID = " + id + ";");
	}

	public void updateRawData(RawData rd) {
		deleteRawData(rd);
		for (Object o : rd.asSql()) {
			executeResultlessSQL((String) o);
		}
	}

	public int getNumberOfRawRecords() throws DatabaseException {
		try {
			ResultSet rs
				= executeSQLWithResult(
					"SELECT COUNT(*) \"Count\" FROM Raw;");

			rs.first();
			return rs.getInt("Count");
		}

		catch (Exception e) {
			throw new DatabaseException(
				"CSV Exception: " + e.getMessage());
		}
	}
	
	private void executeResultlessSQL(String sql) {
		try {
			Class.forName("org.relique.jdbc.csv.CsvDriver");
			Connection c = DriverManager.getConnection("jdbc:relique:csv:" + dbUrl);
			
			Statement s = c.createStatement();
			ResultSet r = s.executeQuery(sql);
			CsvDriver.writeToCsv(r, System.out, true);
			
			r.close();
			s.close();
			c.close();
			
		} catch (ClassNotFoundException exception) {
			exception.printStackTrace();
		} catch (SQLException exception) {
			exception.printStackTrace();
		}
	}
	
	private ResultSet executeSQLWithResult(String sql) {
		try {
			Class.forName("org.relique.jdbc.csv.CsvDriver");
			Connection c = DriverManager.getConnection("jdbc:relique:csv:" + dbUrl);
			
			Statement s = c.createStatement();
			ResultSet r = s.executeQuery(sql);
			CsvDriver.writeToCsv(r, System.out, true);
			
			r.close();
			s.close();
			c.close();
			
			return r;
			
		} catch (ClassNotFoundException exception) {
			exception.printStackTrace();
		} catch (SQLException exception) {
			exception.printStackTrace();
		}
		return null;
	}
}
